const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

/**
 * 数据库结构导出脚本
 * 用于生成数据库结构SQL文件，便于同步到线上环境
 */

async function exportDatabaseSchema() {
  let connection;
  
  try {
    // 创建数据库连接
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASS || '',
      database: process.env.DB_NAME || 'llz_project'
    });

    console.log('✅ 数据库连接成功');

    // 获取所有表名
    const [tables] = await connection.execute('SHOW TABLES');
    const tableNames = tables.map(row => Object.values(row)[0]);
    
    console.log(`📋 发现 ${tableNames.length} 个表:`, tableNames.join(', '));

    let sqlContent = '';
    sqlContent += `-- 数据库结构导出\n`;
    sqlContent += `-- 生成时间: ${new Date().toISOString()}\n`;
    sqlContent += `-- 数据库: ${process.env.DB_NAME || 'llz_project'}\n\n`;
    
    sqlContent += `-- 创建数据库\n`;
    sqlContent += `CREATE DATABASE IF NOT EXISTS \`${process.env.DB_NAME || 'llz_project'}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n`;
    sqlContent += `USE \`${process.env.DB_NAME || 'llz_project'}\`;\n\n`;

    // 为每个表生成CREATE TABLE语句
    for (const tableName of tableNames) {
      console.log(`📝 导出表结构: ${tableName}`);
      
      // 获取表结构
      const [createTableResult] = await connection.execute(`SHOW CREATE TABLE \`${tableName}\``);
      const createTableSQL = createTableResult[0]['Create Table'];
      
      sqlContent += `-- 表结构: ${tableName}\n`;
      sqlContent += `DROP TABLE IF EXISTS \`${tableName}\`;\n`;
      sqlContent += createTableSQL + ';\n\n';
      
      // 获取索引信息
      const [indexes] = await connection.execute(`SHOW INDEX FROM \`${tableName}\``);
      const customIndexes = indexes.filter(idx => 
        idx.Key_name !== 'PRIMARY' && 
        !idx.Key_name.startsWith('FK_') &&
        idx.Index_type !== 'FULLTEXT'
      );
      
      if (customIndexes.length > 0) {
        sqlContent += `-- 索引: ${tableName}\n`;
        const uniqueIndexes = new Set();
        customIndexes.forEach(idx => {
          if (!uniqueIndexes.has(idx.Key_name)) {
            uniqueIndexes.add(idx.Key_name);
            const indexType = idx.Non_unique === 0 ? 'UNIQUE INDEX' : 'INDEX';
            sqlContent += `CREATE ${indexType} \`${idx.Key_name}\` ON \`${tableName}\` (\`${idx.Column_name}\`);\n`;
          }
        });
        sqlContent += '\n';
      }
    }

    // 保存到文件
    const outputDir = path.join(__dirname, '../output');
    if (!fs.existsSync(outputDir)) {
      fs.mkdirSync(outputDir, { recursive: true });
    }
    
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-').split('T')[0];
    const filename = `database_schema_${timestamp}.sql`;
    const filepath = path.join(outputDir, filename);
    
    fs.writeFileSync(filepath, sqlContent, 'utf8');
    
    console.log(`\n✅ 数据库结构导出完成!`);
    console.log(`📁 文件保存位置: ${filepath}`);
    console.log(`📊 导出统计:`);
    console.log(`   - 表数量: ${tableNames.length}`);
    console.log(`   - 文件大小: ${(fs.statSync(filepath).size / 1024).toFixed(2)} KB`);
    
    // 生成部署说明
    const deployInstructions = `
-- 部署说明:
-- 1. 将此文件上传到线上服务器
-- 2. 在线上MySQL中执行: mysql -u username -p database_name < ${filename}
-- 3. 验证表结构: SHOW TABLES; 和 DESCRIBE table_name;
-- 4. 应用增量更新: mysql -u username -p database_name < scripts/update_payments_table.sql
`;
    
    fs.appendFileSync(filepath, deployInstructions);
    
    console.log(`\n📋 部署说明已添加到SQL文件末尾`);
    console.log(`\n🚀 接下来的步骤:`);
    console.log(`   1. 备份线上数据库`);
    console.log(`   2. 上传 ${filename} 到服务器`);
    console.log(`   3. 在线上执行SQL文件`);
    console.log(`   4. 应用增量更新脚本`);
    console.log(`   5. 验证数据库结构`);
    
  } catch (error) {
    console.error('❌ 导出失败:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

// 执行导出
if (require.main === module) {
  exportDatabaseSchema();
}

module.exports = exportDatabaseSchema;